package com.customization.huangwm.workflowNodeAction.SW003;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.api.customization.huangwm.dao.SW003.LoggingDao;
import com.customization.huangwm.utils.DBTools;
import com.customization.huangwm.utils.DateTools;
import com.customization.huangwm.utils.HttpRequest;
import com.customization.huangwm.utils.LogTool;
import weaver.conn.RecordSet;
import weaver.conn.RecordSetTrans;
import weaver.formmode.setup.ModeRightInfo;
import weaver.general.BaseBean;
import weaver.interfaces.workflow.action.Action;
import weaver.soa.workflow.request.Property;
import weaver.soa.workflow.request.RequestInfo;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Huangwm
 * @description 物料申领流程 创建提交时校验物料的余数,
 * 审核时校验资产的状态,
 * 退回申请节点时释放物料数量
 * 归档时对接用友接口扣減對應物料數量，并且更新當前OA库存数量 更新资产状态
 */
public class AssetsRequisition_BAK20230223 implements Action {

    private LogTool log = new LogTool("/log/SW003/huangwm/WfAction/AssetsRequisition", false);
    private DBTools dbTools = new DBTools();
    private DateTools dateTools = new DateTools();
    public String type;//submit_create,submit_review,submit_end

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    @Override
    public String execute(RequestInfo requestInfo) {
        BaseBean bean = new BaseBean();

        String requestid = requestInfo.getRequestid();
        String nodeType = requestInfo.getRequestManager().getNodetype();
        String src = requestInfo.getRequestManager().getSrc();
        int creater = requestInfo.getRequestManager().getCreater();
        log.writeLog("物料申领流程>>>" + requestid + ">>" + nodeType + ">>" + src + ">>" + type);
        if (type.equals("") || (!type.equals("submit_create") && !type.equals("submit_review") && !type.equals("submit_end") && !type.equals("submit_reject"))) {
            requestInfo.getRequestManager().setMessageid(requestid);
            requestInfo.getRequestManager().setMessagecontent("未獲取到Action參數type或參數值不正確,請聯繫管理員檢查後台接口動作設置!;");
            return FAILURE_AND_CONTINUE;
        }

        RecordSet rs1 = new RecordSet();
        String tableName = requestInfo.getRequestManager().getBillTableName();
        String billid = requestInfo.getRequestManager().getBillid() + "";
        int userid = requestInfo.getRequestManager().getUserId();
        Map<String,String> main = new HashMap<>();
        Property[] properties = requestInfo.getMainTableInfo().getProperty();
        for (Property property : properties){
            main.put(property.getName().toLowerCase(),property.getValue());
        }
        log.writeLog(JSONObject.toJSONString(main));
        RecordSetTrans rst = new RecordSetTrans();
        rst.setAutoCommit(false);

        //退回 释放物料数量
        if (src.equals("reject")) {
            if(rs1.execute("select hasOut from "+tableName+" where requestid="+requestid) && rs1.next()){
                int hasOut = rs1.getInt(1);
                if(hasOut==1){
                    try {
                        RecordSetTrans rst1 = new RecordSetTrans();
                        rst1.setAutoCommit(false);
                        String sql1 = "update uf_003_AssteInfo set inApplicationQuantity=isnull(inApplicationQuantity,0)-dt.reviewQuantity,\n" +
                                "InventoryQuantity=InventoryQuantity+dt.reviewQuantity\n" +
                                "from (select isnull(sum(reviewQuantity),0)reviewQuantity,assetCode,mainid from \n" +
                                tableName+"_dt2 where mainid="+billid+" group by assetCode,mainid) dt where uf_003_AssteInfo.id=dt.assetCode and dt.mainid="+billid;
                        if (rs1.getDBType().equals("oracle")) {
                            sql1 = "update uf_003_AssteInfo set inApplicationQuantity=NVL(inApplicationQuantity,0)-dt.reviewQuantity,\n" +
                                    "InventoryQuantity=InventoryQuantity+dt.reviewQuantity\n" +
                                    "from (select NVL(sum(reviewQuantity),0)reviewQuantity,assetCode,mainid from \n" +
                                    tableName+"_dt2 where mainid="+billid+" group by assetCode,mainid) dt where uf_003_AssteInfo.id=dt.assetCode and dt.mainid="+billid;
                        }
                        log.writeLog("退回釋放SQL：" + sql1);

                        boolean result = rst1.execute(sql1);
                        if (!result) {
                            rst1.rollback();
                            requestInfo.getRequestManager().setMessageid(requestid);
                            requestInfo.getRequestManager().setMessagecontent("退回釋放物料數量失敗,SQL執行異常;\n" + sql1);
                            return FAILURE_AND_CONTINUE;
                        } else {
                            rst1.commit();
                        }
                    } catch (Exception e) {
                        requestInfo.getRequestManager().setMessageid(requestid);
                        requestInfo.getRequestManager().setMessagecontent("退回釋放物料數量失敗,程序異常;\n" + e.getMessage());
                        return FAILURE_AND_CONTINUE;
                    }
                }else{
                    return SUCCESS;
                }
            }else{
                requestInfo.getRequestManager().setMessageid(requestid);
                requestInfo.getRequestManager().setMessagecontent("退回釋放物料數量失敗,判斷是否審核出庫異常;\n");
                return FAILURE_AND_CONTINUE;
            }

        } else {
            if (type.equals("submit_create")) {
                //创建提交 先校验数量，校验通过则冻结
                try {
                    //查詢關聯申請的物料剩餘數量是否足夠
//                    String sql1 = "select reviewQuantity,InventoryQuantity,(InventoryQuantity-reviewQuantity) as chae " +
//                            "\n from " + tableName + "_dt2 dt left join uf_003_AssteInfo ass " +
//                            "\n on dt.assetCode=ass.id where  ass.id=dt.assetCode and dt.mainid=" + billid + " order by dt.id asc";
                    String sql1 = "select ass.assetCode,reviewQuantity ,isnull(InventoryQuantity,0)InventoryQuantity,(InventoryQuantity-reviewQuantity) as chae from (\n" +
                            "select isnull(sum(reviewQuantity),0)reviewQuantity,assetCode,mainid from "+tableName+"_dt2 where mainid="+billid+" group by assetCode,mainid \n" +
                            ")dt left join uf_003_AssteInfo ass\n" +
                            "on dt.assetCode=ass.id where  ass.id=dt.assetCode and mainid="+billid;
                    //更新物料信息表中對應的申請中數量和剩餘庫存
                    String sql2 ="update uf_003_AssteInfo set inApplicationQuantity=isnull(inApplicationQuantity,0)+(select reviewQuantity  from( select   assetCode,mainid,sum(reviewQuantity ) as reviewQuantity  from "+tableName+"_dt2 group by assetCode,mainid) t where uf_003_AssteInfo.id=t.assetCode and mainid="+billid+" ),\n" +
                            " InventoryQuantity=InventoryQuantity-(select reviewQuantity  from( select  assetCode,mainid,sum(reviewQuantity ) as reviewQuantity  from "+tableName+"_dt2 group by assetCode,mainid) t where uf_003_AssteInfo.id=t.assetCode and mainid="+billid+")\n" +
                            " where id in(select assetCode from  "+tableName+"_dt2 where mainid="+billid+")";
                    if (rs1.getDBType().equals("oracle")) {
                        sql1 = "select ass.assetCode,reviewQuantity ,NVL(InventoryQuantity,0)InventoryQuantity,(InventoryQuantity-reviewQuantity) as chae from (\n" +
                                "select NVL(sum(reviewQuantity),0)reviewQuantity,assetCode,mainid from "+tableName+"_dt2 where mainid="+billid+" group by assetCode,mainid \n" +
                                ")dt left join uf_003_AssteInfo ass\n" +
                                "on dt.assetCode=ass.id where  ass.id=dt.assetCode and mainid="+billid;
                        //sql2 = "update uf_003_AssteInfo set inApplicationQuantity=NVL(inApplicationQuantity,0)+(select reviewQuantity from " + tableName + "_dt2 where assetCode=uf_003_AssteInfo.id and mainid=" + billid + ")," +
                                //"\n InventoryQuantity=InventoryQuantity-(select reviewQuantity from " + tableName + "_dt2 where assetCode=uf_003_AssteInfo.id and mainid=" + billid + ")" +
                                //"\n where id in(select assetCode from  " + tableName + "_dt2 where mainid=" + billid + ")";
                        sql2 ="update uf_003_AssteInfo set inApplicationQuantity=NVL(inApplicationQuantity,0)+(select reviewQuantity  from( select   assetCode,mainid,sum(reviewQuantity ) as reviewQuantity  from "+tableName+"_dt2 group by assetCode,mainid) t where uf_003_AssteInfo.id=t.assetCode and mainid="+billid+" ),\n" +
                                " InventoryQuantity=InventoryQuantity-(select reviewQuantity  from( select  assetCode,mainid,sum(reviewQuantity ) as reviewQuantity  from "+tableName+"_dt2 group by assetCode,mainid) t where uf_003_AssteInfo.id=t.assetCode and mainid="+billid+")\n" +
                                " where id in(select assetCode from  "+tableName+"_dt2 where mainid="+billid+")";
                    }
                    log.writeLog("查詢數量SQL：" + sql1);
                    log.writeLog("提交凍結SQL：" + sql2);

                    rs1.execute(sql1);
                    int i = 1;
                    while (rs1.next()) {
                        Double chae = rs1.getDouble("chae");
                        Double InventoryQuantity = rs1.getDouble("InventoryQuantity");
                        Double reviewQuantity  = rs1.getDouble("reviewQuantity");
                        String assetCode = rs1.getString("assetCode");
                        if (chae < 0) {
                            requestInfo.getRequestManager().setMessageid(requestid);
                            requestInfo.getRequestManager().setMessagecontent("<span class=\"importantInfo\">物料编码["+assetCode+"]申请数量合计[" + reviewQuantity  + "]超過剩餘數量[" + InventoryQuantity + "]</span>");
                            return FAILURE_AND_CONTINUE;
                        }
                        i++;
                    }

                    //20221019 增加倉庫庫存校驗
                    boolean f = true;
                    String sql3 = "select dt2.assetcode,reviewQuantity,NVL(w.num1,0)num1,NVL(w.num2,0)num2,NVL(w.num3,0)num3,\n" +
                            "NVL(w.num4,0)num4,w.id as wid  from "+tableName+"_dt2  dt2  left join uf_003_WarehouseNum w \n" +
                            "on dt2.assetcode=w.assetcode where dt2.field1=w.wareHouse and mainid="+billid;
                    RecordSet checkWarehouse = new RecordSet();
                    checkWarehouse.execute(sql3);
                    if(checkWarehouse.getCounts()>0){
                        while (checkWarehouse.next()){
                            String assetCode = checkWarehouse.getString("assetcode");
                            Double reviewQuantity = checkWarehouse.getDouble("reviewQuantity");
                            Double num1 = checkWarehouse.getDouble("num1");//總庫存
                            Double num2 = checkWarehouse.getDouble("num2");//已出庫
                            Double num3 = checkWarehouse.getDouble("num3");//剩餘庫存
                            Double num4 = checkWarehouse.getDouble("num4");//出庫中
                            String wid = checkWarehouse.getString("wid");//倉庫記錄表中的id
                            if(num3<reviewQuantity){
                                f = false;
                                rst.rollback();
                                requestInfo.getRequestManager().setMessageid(requestid);
                                requestInfo.getRequestManager().setMessagecontent("<span class=\"importantInfo\">物料编码["+assetCode+"]倉庫庫存餘量不足</span>");
                                return FAILURE_AND_CONTINUE;
                            }else {
                                boolean thisCheck = rst.executeUpdate("update uf_003_WarehouseNum set num4=NVL(num4,0)+"+reviewQuantity+",num3=NVL(num3,0)-"+reviewQuantity +" where id="+wid);
                                log.writeLog("申請時扣減倉庫庫存結果："+thisCheck);
                                log.writeLog("扣減倉庫庫存SQL：update uf_003_WarehouseNum set num4=NVL(num4,0)+"+reviewQuantity+",num3=NVL(num3,0)-"+reviewQuantity +" where id="+wid);
                                if(rst.getUpdateCount()<=0 || !thisCheck){
                                    f = false;
                                    rst.rollback();
                                    requestInfo.getRequestManager().setMessageid(requestid);
                                    requestInfo.getRequestManager().setMessagecontent("<span class=\"importantInfo\">物料编码["+assetCode+"]更新倉庫出庫中數量失敗</span>");
                                    return FAILURE_AND_CONTINUE;
                                }
                            }
                        }
                    }else{
                        rst.rollback();
                        requestInfo.getRequestManager().setMessageid(requestid);
                        requestInfo.getRequestManager().setMessagecontent("<span class=\"importantInfo\">通過申請記錄關聯倉庫庫存查詢未獲取到記錄</span>");
                        return FAILURE_AND_CONTINUE;
                    }
                    if(!f){
                        rst.rollback();
                        requestInfo.getRequestManager().setMessageid(requestid);
                        requestInfo.getRequestManager().setMessagecontent("<span class=\"importantInfo\">倉庫庫存更新失敗</span>");
                        return FAILURE_AND_CONTINUE;
                    }

                    boolean result = rst.executeUpdate(sql2);
                    if (!result) {
                        rst.rollback();
                        requestInfo.getRequestManager().setMessageid(requestid);
                        requestInfo.getRequestManager().setMessagecontent("創建提交凍結物料數量失敗,SQL執行異常;");
                        return FAILURE_AND_CONTINUE;
                    } else {
                        rst.commit();
                    }
                } catch (Exception e) {
                    e.getMessage();
                    rst.rollback();
                    requestInfo.getRequestManager().setMessageid(requestid);
                    requestInfo.getRequestManager().setMessagecontent("創建提交凍結物料數量失敗,程序異常;\n" + e.getMessage());
                    return FAILURE_AND_CONTINUE;
                }
            } else if (type.equals("submit_review")) {
                //审核提交，只校验资产的状态
                try {
                    //查詢是資產的時候所選擇資產狀態是否為0（空間）
                    String sql1 = "select dt.id,ass.assetStatus,ass.assetCode " +
                            "\n from " + tableName + "_dt2 dt left join uf_003_AssetInfo2 ass" +
                            "\n on dt.mainAssetCode=ass.id" +
                            "\n where dt.isAsset=0 and dt.mainid=" + billid + " order by dt.id asc ";
                    //資產的申領需要更新資產狀態為审批中
                    String sql2 = "update uf_003_AssetInfo2 set assetStatus='Approving' from " + tableName + "_dt2 dt " +
                            "\n where uf_003_AssetInfo2.id=dt.mainAssetCode and dt.mainid=" + billid;
                    if (rs1.getDBType().equals("oracle")) {
                        //sql2 = "update uf_003_AssetInfo2 set assetStatus=(select 'B' from " + tableName + "_dt2 dt where  uf_003_AssetInfo2.id=dt.mainAssetCode and dt.mainid=" + billid + ")" +
                        sql2 = "update uf_003_AssetInfo2 set assetStatus='Approving'" +
                                "\n where id in(select mainAssetcode from  " + tableName + "_dt2 where mainid=" + billid + ")";
                    }
                    log.writeLog("查詢資產狀態SQL：" + sql1);
                    log.writeLog("更新資產狀態SQL：" + sql2);
                    rs1.execute(sql1);
                    while (rs1.next()) {
                        String assetStatus = rs1.getString("assetStatus");
                        String assetCode = rs1.getString("assetCode");
                        if (!assetStatus.equals("Stock") && !assetStatus.equals("") && !assetStatus.equals("NULL")) {
                            rst.rollback();
                            requestInfo.getRequestManager().setMessageid(requestid);
                            requestInfo.getRequestManager().setMessagecontent("對應資產["+assetCode+"]已被領用");
                            return FAILURE_AND_CONTINUE;
                        }
                    }
                    boolean result = rst.execute(sql2);
                    boolean hasOut = rst.execute("update "+tableName+" set hasOut=1 where requestid="+requestid);

                    if (!result) {
                        rst.rollback();
                        requestInfo.getRequestManager().setMessageid(requestid);
                        requestInfo.getRequestManager().setMessagecontent("審核更新資產狀態失敗,執行SQL異常;\n" + sql2);
                        return FAILURE_AND_CONTINUE;
                    } else if(!hasOut){
                        rst.rollback();
                        requestInfo.getRequestManager().setMessageid(requestid);
                        requestInfo.getRequestManager().setMessagecontent("審核更新出庫狀態失敗,執行SQL異常;\n" + "update "+tableName+" set hasOut=1 where requestid="+requestid);
                        return FAILURE_AND_CONTINUE;
                    } else{
                        rst.commit();
                    }
                } catch (Exception e) {
                    rst.rollback();
                    requestInfo.getRequestManager().setMessageid(requestid);
                    requestInfo.getRequestManager().setMessagecontent("審核更新資產狀態失敗,程序異常;\n" + e.getMessage());
                    return FAILURE_AND_CONTINUE;
                }

            } else if (type.equals("submit_end")) {
                if(!main.containsKey("lastoperator")){
                    rst.rollback();
                    requestInfo.getRequestManager().setMessageid(requestid);
                    requestInfo.getRequestManager().setMessagecontent("未獲取到最後審核人字段lastoperator");
                    return FAILURE_AND_CONTINUE;
                }
                String lastoperator = main.get("lastoperator");
                if( lastoperator.equals("") ){
                    lastoperator = dbTools.getCurrentoperatorFromNodeName(tableName,requestid,"管理部門審核");
                }
                if(lastoperator.equals("") ){
                    rst.rollback();
                    requestInfo.getRequestManager().setMessageid(requestid);
                    requestInfo.getRequestManager().setMessagecontent("未獲取到最後審核人字段值");
                    return FAILURE_AND_CONTINUE;
                }
                String sql2 = "update uf_003_AssteInfo set inApplicationQuantity=\n" +
                        "isnull(inApplicationQuantity,0)-(select reviewQuantity  from( select   assetCode,mainid,sum(reviewQuantity ) as reviewQuantity  from "+tableName+"_dt2 group by assetCode,mainid) t where uf_003_AssteInfo.id=t.assetCode and mainid="+billid+"),\n" +
                        "usedQuantity=isnull(usedQuantity,0)+(select reviewQuantity  from( select  assetCode,mainid,sum(reviewQuantity ) as reviewQuantity  from "+tableName+"_dt2 group by assetCode,mainid) t where uf_003_AssteInfo.id=t.assetCode and mainid="+billid+")\n" +
                        "where id in(select assetCode from  "+tableName+"_dt2 where mainid="+billid+")";
                //資產的申領需要更新資產狀態為借出
                String sql3 = "update uf_003_AssetInfo2 set assetStatus='Operating' from " + tableName + "_dt2 dt " +
                        "\n where uf_003_AssetInfo2.id=dt.mainAssetCode and dt.mainid=" + billid;
                if (rs1.getDBType().equals("oracle")) {
//                        sql2 = "update uf_003_AssteInfo set inApplicationQuantity=NVL(inApplicationQuantity,0)-(select reviewQuantity from " + tableName + "_dt2 where assetCode=uf_003_AssteInfo.id and mainid=" + billid + ")," +
//                                "\n usedQuantity=usedQuantity+(select reviewQuantity from " + tableName + "_dt2 where assetCode=uf_003_AssteInfo.id and mainid=" + billid + ")" +
//                                "\n where id in(select assetCode from  " + tableName + "_dt2 where mainid=" + billid + ")";
                    sql2 = "update uf_003_AssteInfo set inApplicationQuantity=\n" +
                            "NVL(inApplicationQuantity,0)-(select reviewQuantity  from( select   assetCode,mainid,sum(reviewQuantity ) as reviewQuantity  from "+tableName+"_dt2 group by assetCode,mainid) t where uf_003_AssteInfo.id=t.assetCode and mainid="+billid+"),\n" +
                            "usedQuantity=NVL(usedQuantity,0)+(select reviewQuantity  from( select  assetCode,mainid,sum(reviewQuantity ) as reviewQuantity  from "+tableName+"_dt2 group by assetCode,mainid) t where uf_003_AssteInfo.id=t.assetCode and mainid="+billid+")\n" +
                            "where id in(select assetCode from  "+tableName+"_dt2 where mainid="+billid+")";
                    //sql2 = "update uf_003_AssetInfo2 set assetStatus=(select 'B' from " + tableName + "_dt2 dt where  uf_003_AssetInfo2.id=dt.mainAssetCode and dt.mainid=" + billid + ")" +
                    sql3 = "update uf_003_AssetInfo2 set assetStatus='Operating'" +
                            "\n where id in(select mainAssetcode from  " + tableName + "_dt2 where mainid=" + billid + ")";
                }
                try{
                    log.writeLog("歸檔扣減物料數量SQL：" + sql2);
                    log.writeLog("歸檔更新资产状态SQL：" + sql3);
                    boolean result1 = rst.execute(sql2);
                    boolean result2 = rst.execute(sql3);
                    log.writeLog("SQL執行結果;result1=" + result1+",result2="+result2);

                    //20221019 物料领用记录走逻辑写入 合并同一物料的审核数量（非资产型），资产型的还是每条单独插入
                    /*
                    String record = "insert into uf_003_applicationRecord(formmodeid,modedatacreater,modedatacreatertype,modedatacreatedate,modedatacreatetime,MODEUUID" +
                            ",applicationRequestid,applicationUser,applicationDate,applicationDep" +
                            ",assetCode,assetName,managerDep,applicationQuantity,mainAssetCode,returnQuantity" +
                            ",isAsset,oper_staus,quantityInReturn,xmbm,field1)" +
                            "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                    String uf_003_applicationRecord_Modeid = bean.getPropValue("SW003", "uf_003_applicationRecord_Modeid");
                    String sql4 = "select isAsset,assetCode,sum(reviewQuantity)reviewQuantity,mainAssetCode from "+tableName+"_dt2 where mainid="+billid+" group by isAsset,assetCode,mainAssetCode";
                    RecordSet getGroup = new RecordSet();
                    getGroup.execute(sql4);
                    while (getGroup.next()){
                        String assetCode = getGroup.getString("assetCode");//id
                        String mainAssetCode = getGroup.getString("mainAssetCode");
                        Double reviewQuantity = getGroup.getDouble("reviewQuantity");
                        Map<String,String> assetInfo = dbTools.getAssetAllFieldoByID(rs1,assetCode);
                        log.writeLog("assetcodeid="+assetCode);
                        log.writeLog(JSONObject.toJSONString(assetInfo));
                        String modedatacreatedate = dateTools.getCurrentDate();
                        String modedatacreatetime = dateTools.getCurrentTime();
                        String uuid = dateTools.getUUID();
                        Object[] objects = new Object[]{
                                uf_003_applicationRecord_Modeid, userid, 0, modedatacreatedate, modedatacreatetime, uuid,
                                requestid,main.get("applicationuser"),main.get("applicationdate"),main.get("applicationdep"),
                                assetCode, assetInfo.get("assetname"), assetInfo.get("managedep"), reviewQuantity, mainAssetCode, 0,
                                assetInfo.get("isasset"),0,0,getProjectCode(billid,tableName+"_dt2",assetCode),main.get("warehouse")
                        };
                        boolean thisInsert = rst2.executeUpdate(record,objects);
                        if(!thisInsert){
                            rst2.rollback();
                            requestInfo.getRequestManager().setMessageid(requestid);
                            requestInfo.getRequestManager().setMessagecontent("<span class=\"importantInfo\">生成出庫記錄失敗</span>");
                            return FAILURE_AND_CONTINUE;
                        }else{
                            log.writeLog("开始进行权限重构=>>>" + dateTools.getCurrentTimes());
                            RecordSet right = new RecordSet();
                            String sqlSearch = "select id from uf_003_applicationRecord where MODEUUID='" + uuid + "'";
                            right.execute(sqlSearch);
                            if (right.next()) {
                                log.writeLog("本次插入 uf_003_applicationRecord 表的数据id： " + right.getInt("id"));
                                refreshRight(userid, Integer.parseInt(uf_003_applicationRecord_Modeid), right.getInt("id"));
                            }
                            log.writeLog("权限重构结束=>>>" + dateTools.getCurrentTimes());
                        }
                    }
                    */
                    //20221103 取消自定义写入领用记录

                    //20221019 歸檔倉庫庫存數量扣減
                    boolean f = true;
//                    RecordSetTrans rst3= new RecordSetTrans();
//                    rst3.setAutoCommit(false);
                    String sql5 = "select dt2.assetcode,reviewQuantity,NVL(w.num1,0)num1,NVL(w.num2,0)num2,NVL(w.num3,0)num3,\n" +
                            "NVL(w.num4,0)num4,w.id as wid  from "+tableName+"_dt2  dt2  left join uf_003_WarehouseNum w \n" +
                            "on dt2.assetcode=w.assetcode where dt2.field1=w.wareHouse and mainid="+billid;
                    RecordSet checkWarehouse = new RecordSet();
                    checkWarehouse.execute(sql5);
                    if(checkWarehouse.getCounts()>0){
                        log.writeLog("歸檔執行倉庫庫存扣減:"+sql5);
                        while (checkWarehouse.next()){
                            try {
                                String assetCode = checkWarehouse.getString("assetCode");
                                Double reviewQuantity = checkWarehouse.getDouble("reviewQuantity");
                                Double num1 = checkWarehouse.getDouble("num1");//總庫存
                                Double num2 = checkWarehouse.getDouble("num2");//已出庫
                                Double num3 = checkWarehouse.getDouble("num3");//剩餘庫存
                                Double num4 = checkWarehouse.getDouble("num4");//出庫中
                                String wid = checkWarehouse.getString("wid");//倉庫記錄表中的id
                                String upsql = "update uf_003_WarehouseNum set num4=NVL(num4,0)-" + reviewQuantity + ",num2=NVL(num2,0)+" + reviewQuantity + " where id=" + wid;
                                log.writeLog("更新倉庫庫存sql："+upsql);
                                boolean thisCheck = rst.execute(upsql);
                                if (!thisCheck) {
                                    f = false;
                                    rst.rollback();
                                    requestInfo.getRequestManager().setMessageid(requestid);
                                    requestInfo.getRequestManager().setMessagecontent("<span class=\"importantInfo\">物料编码[" + assetCode + "]更新倉庫已出庫數量失敗</span>");
                                    return FAILURE_AND_CONTINUE;
                                }
                                log.writeLog("assetid=" + assetCode + " 倉庫記錄id=" + wid + "更新完成");
                            }catch (Exception e){
                                rst.rollback();
                                e.printStackTrace();
                                requestInfo.getRequestManager().setMessageid(requestid);
                                requestInfo.getRequestManager().setMessagecontent("<span class=\"importantInfo\">物料更新倉庫已出庫數量異常"+e.getMessage()+"</span>");
                                return FAILURE_AND_CONTINUE;
                            }
                        }
                    }else{
                        rst.rollback();
                        requestInfo.getRequestManager().setMessageid(requestid);
                        requestInfo.getRequestManager().setMessagecontent("<span class=\"importantInfo\">通過申請記錄關聯倉庫庫存查詢未獲取到記錄</span>");
                        return FAILURE_AND_CONTINUE;
                    }
                    if(!f){
                        rst.rollback();
                        requestInfo.getRequestManager().setMessageid(requestid);
                        requestInfo.getRequestManager().setMessagecontent("<span class=\"importantInfo\">倉庫庫存更新失敗</span>");
                        return FAILURE_AND_CONTINUE;
                    }
                    if (!result2) {
                        rst.rollback();
                        requestInfo.getRequestManager().setMessageid(requestid);
                        requestInfo.getRequestManager().setMessagecontent("归档更新资产状态失敗,SQL執行異常;\n" + sql3);
                        return FAILURE_AND_CONTINUE;
                    }
                    if (!result1) {
                        rst.rollback();
                        requestInfo.getRequestManager().setMessageid(requestid);
                        requestInfo.getRequestManager().setMessagecontent("归档扣減物料餘數失敗,SQL執行異常;\n" + sql2);
                        return FAILURE_AND_CONTINUE;
                    } else {
                        try {

                            //先對接用友 再提交更新，對接失敗則rollback
                            String eop_reqUrl = new BaseBean().getPropValue("SW003", "eop_reqUrl");
                            if (eop_reqUrl.isEmpty()) {
                                eop_reqUrl = "https://testeop.macaowater.com";
                            }
                            String eop_reqToken = new BaseBean().getPropValue("SW003", "eop_reqToken");
                            if (eop_reqToken.isEmpty()) {
                                eop_reqToken = "EapUfida20200630D1207F4TEST";
                            }
                            HttpRequest http = new HttpRequest();
                            JSONObject params = new JSONObject();
                            params.put("reqNb", http.getTimestamp());
                            params.put("reqLang", "zh");
                            params.put("reqToken", eop_reqToken);
                            Map<String, Object> reqData = new HashMap<>();
                            Map<String, Object> billHead = new HashMap<>();
                            billHead.put("id", requestid);
                            billHead.put("cbilltypecode", "4D");
                            billHead.put("dbilldate", dateTools.getCurrentDate());
                            billHead.put("pk_corp", "MW");
                            billHead.put("vbillcode", requestid);
                            billHead.put("cdispatcherid", "21");
                            billHead.put("cdeptid", dbTools.getYyDepCode(lastoperator));
                            billHead.put("pk_calbody", "MW");
                            billHead.put("cwarehouseid", "01");
                            //billHead.put("coperatorid", dbTools.getWorkCode(creater + ""));
                            billHead.put("coperatorid",dbTools.getWorkCode(lastoperator));
                            billHead.put("fbillflag", "2");
                            billHead.put("vnote", "");
                            billHead.put("vuserdef20", main.get("docno"));
                            //添加billHead至reqData
                            reqData.put("billHead", billHead);
                            RecordSet rs3 = new RecordSet();
                            List<Map<String, Object>> billBodys = new ArrayList<>();
                            rs1.execute("select  sum(REVIEWQUANTITY) as reviewQuantity,assetCode,projectcode from "+tableName+"_dt2 where mainid="+billid +
                                    "group by assetCode,projectcode");
                            while (rs1.next()) {
                                Double reviewQuantity = rs1.getDouble("reviewQuantity");
                                if (reviewQuantity > 0) {
                                    Map<String, Object> billBody = new HashMap<>();
                                    String assetCode = dbTools.getAssetCode(rs3, rs1.getString("assetCode"));
                                    billBody.put("cinventoryid", assetCode);
                                    String cprojectid = rs1.getString("projectcode");
                                    billBody.put("cprojectid", cprojectid);
                                    String dbizdate = requestInfo.getRequestManager().getCreatedate();
                                    billBody.put("dbizdate", dateTools.getCurrentDate());

                                    Double nprice = 0.0;
                                    Double nmny = 0.0;
                                    billBody.put("noutnum", reviewQuantity);
                                    billBody.put("nprice", 0.00);
                                    billBody.put("nmny", 0.00);
                                    billBodys.add(billBody);
                                }

                            }
                            RecordSet rs2 = new RecordSet();
                            rs2.execute("select  sum(REVIEWQUANTITY) as reviewQuantity,assetCode,field1 from "+tableName+"_dt2 where mainid="+billid +
                                    "group by assetCode,field1");
                            List<Map<String, String>> logList = new ArrayList<>();//日志记录
                            while (rs2.next()){
                                Double reviewQuantity = rs2.getDouble("reviewQuantity");
                                if (reviewQuantity > 0) {
                                    String assetCode = dbTools.getAssetCode(rs3, rs2.getString("assetCode"));
                                    Map<String, String> logMap = new HashMap();
                                    logMap.put("assetCode", assetCode);
                                    logMap.put("quantity", reviewQuantity + "");
                                    logMap.put("requestNumber", requestid);
                                    logMap.put("reason", "OA出庫");
                                    String field1 = rs2.getString("field1");
                                    String field2 = rs2.getString("field2");
                                    logMap.put("field1", field1);
                                    logMap.put("field2", field2);
                                    logMap.put("userid", rs2.getString("userid"));
                                    logList.add(logMap);
                                }
                            }
                            LoggingDao loggingDao = new LoggingDao();
                            try {
                                for (int i = 0; i < logList.size(); i++) {
                                    Map<String, String> log = logList.get(i);
                                    loggingDao.wrtieInventoryLog(rst,"0", log.get("assetCode"),
                                            Double.parseDouble(log.get("quantity")), log.get("requestNumber"), log.get("reason"), log.get("field1"), log.get("field2"),
                                            "1",log.get("userid"),"","","","");
                                }
                            } catch (Exception e) {
                                rst.rollback();
                                e.printStackTrace();
                                requestInfo.getRequestManager().setMessageid(requestid);
                                requestInfo.getRequestManager().setMessagecontent("記錄出庫日志異常;\n" + e.getMessage());
                                return FAILURE_AND_CONTINUE;
                            }
                            //添加billBody至reqData
                            reqData.put("billBody", billBodys);
                            params.put("reqData", reqData);
                            if(billBodys.size()>0){
                                log.writeLog("---傳入用友參數如下---");
                                log.writeLog(JSON.toJSONString(params));
                                JSONObject json = http.CallApiPost(eop_reqUrl + "/eop/eapToUfida/icBillUpdate", JSONObject.toJSONString(params));
                                log.writeLog("---傳入用友結果如下---");
                                log.writeLog(JSON.toJSONString(json));
                                loggingDao.wrtieLog("0", "/eop/eapToUfida/icBillUpdate", JSON.toJSONString(params), JSON.toJSONString(json));
                                //对接用友
                                if (json == null || !json.getString("state").equals("0")) {
                                    rst.rollback();
                                    requestInfo.getRequestManager().setMessageid(requestid);
                                    requestInfo.getRequestManager().setMessagecontent("物料出庫傳入用友失敗;\n" + JSON.toJSONString(json));
                                    return FAILURE_AND_CONTINUE;
                                } else {
                                    rst.commit();//提交更新
                                }
                            }else{
                                log.writeLog("---没有需要審批的數據 不傳用友 回滾---"+rst.rollback());
                            }
                        }catch (Exception e){
                            e.printStackTrace();
                            rst.rollback();
                            requestInfo.getRequestManager().setMessageid(requestid);
                            requestInfo.getRequestManager().setMessagecontent("归档对接用友接口异常 ;\n" + e.getMessage());
                            return FAILURE_AND_CONTINUE;
                        }
                    }
                } catch (Exception e) {
                    rst.rollback();
                    e.printStackTrace();
                    requestInfo.getRequestManager().setMessageid(requestid);
                    requestInfo.getRequestManager().setMessagecontent("归档扣減物料餘數失敗,程序異常;\n" + e.getMessage());
                    return FAILURE_AND_CONTINUE;
                }
            }
        }
        log.writeLog("物料領用結束----"+requestid);
        return SUCCESS;
    }

    private String getProjectCode(String billid,String table,String assetCode ){
        RecordSet rs = new RecordSet();
        rs.execute("select projectcode from "+table+" where mainid="+billid+" and assetcode="+assetCode);
        rs.first();
        return rs.getString("projectcode");
    }

    /**
     * 表单建模权限重构方法，重构后才能在台账里查看到数据
     *
     * @param creater    创建人
     * @param formmodeid 模块id
     * @param billid     数据id,即对应uf表里的id
     */
    public void refreshRight(int creater, int formmodeid, int billid) {
        try {
            ModeRightInfo modeRightInfo = new ModeRightInfo();
            //console.info("当前重构数据：creater=" + creater + "  formmodeid=" + formmodeid + "  billid=" + billid);
            modeRightInfo.setNewRight(true);
            modeRightInfo.editModeDataShare(creater, formmodeid, billid);
        } catch (Exception e) {
            log.writeLog("重构代码报错：creater=" + creater + "  formmodeid=" + formmodeid + "  billid=" + billid);
            log.writeLog(e.getMessage());
            e.printStackTrace();
        }
    }
}
